﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace AISFMIOperator
{
    class InsuranceProgram 
    {
        public InsuranceProgram()
        {}

        public int InsuranceProgramID
        { get; set; }

        public string InsuranceProgramName
        { get; set; }

        public double InsuranceProgramSumm
        { get; set; }

        public double InsuranceProgramPremium
        { get; set; }

        public List<InsuranceProgramCoefficient> Coifficients
        { get; set; }

        public List<MedicalService> MedicalServices
        { get; set; }

        public List<Facility> Facilities
        { get; set; }

        public void Save()
        {
            SqlCommand command = new SqlCommand("CreateInsuranceProgram");
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@ins_program_name", SqlDbType.VarChar, 255).Value = InsuranceProgramName;
            command.Parameters.Add("@sum_amount", SqlDbType.Money).Value = InsuranceProgramSumm;
            command.Parameters.Add("@premium_amount", SqlDbType.Money).Value = InsuranceProgramPremium;
            command.Parameters.Add("@ins_program_id", SqlDbType.Int).Direction = ParameterDirection.Output;
            SQLDBLayer.ExecSPNonQuery(command);
            this.InsuranceProgramID = (int)command.Parameters["@ins_program_id"].Value;

            command = new SqlCommand("CreateInsProgramMedicalService");
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@ins_program_id",SqlDbType.Int);
            command.Parameters.Add("@service_id", SqlDbType.Int);

            foreach(MedicalService service in MedicalServices)
            {
                command.Parameters["@ins_program_id"].Value = this.InsuranceProgramID;
                command.Parameters["@service_id"].Value = service.MedicalServiceID;  
                SQLDBLayer.ExecSPNonQuery(command);
            }

            command = new SqlCommand("CreateInsProgramFacility");
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@ins_program_id", SqlDbType.Int);
            command.Parameters.Add("@facility_id", SqlDbType.Int);

            foreach (Facility facility in Facilities)
            {
                command.Parameters["@ins_program_id"].Value = InsuranceProgramID;
                command.Parameters["@facility_id"].Value = facility.FacilityID;
                SQLDBLayer.ExecSPNonQuery(command);
            }

            command = new SqlCommand("CreateInsProgramCoifficient");
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@ins_program_id", SqlDbType.Int);
            command.Parameters.Add("@age_from", SqlDbType.Int);
            command.Parameters.Add("@age_to", SqlDbType.Int);
            command.Parameters.Add("@coifficient", SqlDbType.Float);

            foreach (InsuranceProgramCoefficient coif in Coifficients)
            {
                command.Parameters["@ins_program_id"].Value = InsuranceProgramID;
                command.Parameters["@age_from"].Value = coif.AgeFrom;
                command.Parameters["@age_to"].Value = coif.AgeTo;
                command.Parameters["@coifficient"].Value = coif.Coifficient;
                SQLDBLayer.ExecSPNonQuery(command);
            }
        }

        public void Update()
        {
            SqlCommand command = new SqlCommand("UpdateInsProgram");
            command.CommandType = CommandType.StoredProcedure;
            SQLDBLayer.ExecSPNonQuery(command);
        }

        public void Delete()
        {
            SqlCommand command = new SqlCommand("DeleteinsProgram");
            command.CommandType = CommandType.StoredProcedure;
            //command.Parameters.Add("@address_id", SqlDbType.Int).Value = AddressID;
            SQLDBLayer.ExecSPNonQuery(command);
        }

        public static List<InsuranceProgram> GetAllInsurancePrograms()
        {
            DataTable table = new DataTable();
            List<InsuranceProgram> objects = new List<InsuranceProgram>();
            SqlCommand command = new SqlCommand("GetInsurancePrograms");
            command.CommandType = CommandType.StoredProcedure;

            table = SQLDBLayer.ExecSPTable(command);

            foreach (DataRow row in table.Rows)
            {
                InsuranceProgram ip = new InsuranceProgram();
                ip.InsuranceProgramID = (int)row["ins_program_id"];
                ip.InsuranceProgramName = row["ins_program_name"].ToString();
                ip.InsuranceProgramSumm = float.Parse(row["sum_amount"].ToString());
                ip.InsuranceProgramPremium = float.Parse(row["premium_amount"].ToString());

                command = new SqlCommand("GetInsuranceProgramFacilities");
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@ins_program_id", SqlDbType.Int);
                DataTable facilities = SQLDBLayer.ExecSPTable(command);
                List<Facility> fl = Facility.GetAllFacilities();
                ip.Facilities = new List<Facility>();

                foreach (DataRow facility in facilities.Rows)
                {
                    ip.Facilities.Add((Facility)fl.Where(o => o.FacilityID == (int)facility["facility_id"]).ToList()[0]);
                }

                command = new SqlCommand("GetInsuranceProgramMedicalServices");
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@ins_program_id", SqlDbType.Int);
                DataTable services = SQLDBLayer.ExecSPTable(command);
                List<MedicalService> sl = MedicalService.GetAllMedicalServices();
                ip.MedicalServices = new List<MedicalService>();

                foreach (DataRow service in services.Rows)
                {
                    ip.MedicalServices.Add((MedicalService)sl.Where(o => o.MedicalServiceID == (int)service["service_id"]).ToList()[0]);
                }

                command = new SqlCommand("GetInsuranceProgramCoifficients");
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@ins_program_id", SqlDbType.Int);
                DataTable coifficients = SQLDBLayer.ExecSPTable(command);

                ip.Coifficients = new List<InsuranceProgramCoefficient>();

                foreach (DataRow coif in coifficients.Rows)
                {
                    InsuranceProgramCoefficient ipc = new InsuranceProgramCoefficient();
                    ipc.InsuranceProgramID = (int)coif["ins_program_id"];
                    ipc.AgeFrom =  (int)coif["age_from"];
                    ipc.AgeTo = (int)coif["age_to"];
                    ipc.Coifficient = float.Parse(coif["coifficient"].ToString());

                    ip.Coifficients.Add(ipc);
                }

                objects.Add(ip);
            }

            return objects;
        }

    }
}
